<?php

namespace App\Service;

use App\Models\EwData;
use App\Models\EwBill;
use Phalcon\Mvc\Model\Resultset\Simple as Resultset;
use Phalcon\Db;
class DataService  extends BaseService
{
	const DISPLAY_TRUE = 1;		//显示标识：是
	const DISPLAY_FALSE = 0;	//显示标识：否
	const DEL_TRUE = 1;			//删除标识：是
	const DEL_FALSE = 0;		//删除标识：否
	const FREE_TRUE = 1;		//免费标识：是
	const FREE_FALSE = 0;		//免费标识：否
	const STATE_PAYED = 2;		//支付标识：是
	const STATE_NOTPAY = 1;		//支付标识：否

	/**
	 * [getList 列表]
	 * @param  [type] $params [description]
	 * @return [type]         [description]
	 */
	public static function getList($params, $count = FALSE)
	{

		$memberId = property_exists($params,'member_id') ? $params->member_id : 0;
		$builder = new \Phalcon\Mvc\Model\Query\Builder();

		$builder->columns([self::getTableId() . ' as groupId,d.three_url,d.is_self,
			IFNULL((SELECT count(c.id) FROM App\Models\EwMemberCollect c where c.list_id = d.id AND c.table_id = ' . self::getTableId() . ' AND c.member_id = ' . $memberId . '),0) AS is_collect,
			d.id,d.id as go_url,d.title,IFNULL(d.tags,"") AS tags,IFNULL((SELECT c.name AS cate_name FROM App\Models\EwCats c where d.cate_id = c.id),"免费数据") AS cate_name,
			IFNULL((d.summary),"") AS summary,
			d.thumb,d.sources,d.is_free,d.visit,d.comment,d.create_time,
			IFNULL((SELECT count(bb.id) FROM App\Models\EwBill bb where d.id = bb.data_id AND bb.state = ' . self::STATE_PAYED . ' AND bb.member_id = ' . $memberId . '),0) AS is_pay,
			(SELECT COUNT(b.id) AS pay_num FROM App\Models\EwBill b where d.id = b.data_id AND b.state = ' . self::STATE_PAYED . ') AS pay_num,IFNULL(d.index_by,0) AS index_by,IFNULL(d.reco_by,0) AS reco_by']);
		// $builder->columns([self::getTableId() . ' as groupId,
		// 	IFNULL((SELECT count(c.id) FROM App\Models\EwMemberCollect c where c.list_id = d.id AND c.table_id = ' . self::getTableId() . ' AND c.member_id = ' . $memberId . '),0) AS is_collect,
		// 	d.id,d.go_url,d.title,d.tags,IFNULL((SELECT c.name AS cate_name FROM App\Models\EwCats c where d.cate_id = c.id),0) AS cate_name,d.summary,d.cate_id,d.type_id,d.pics,d.thumb,d.price,d.sources,d.is_free,d.visit,d.comment,
		// 	IFNULL((SELECT count(bb.id) FROM App\Models\EwBill bb where d.id = bb.data_id AND bb.state = ' . self::STATE_PAYED . ' AND bb.member_id = ' . $memberId . '),0) AS is_pay,
		// 	(SELECT COUNT(b.id) AS pay_num FROM App\Models\EwBill b where d.id = b.data_id AND b.state = ' . self::STATE_PAYED . ') AS pay_num']);
		// // IFNULL((SELECT count(c.id) FROM App\Models\EwMemberCollect c where c.list_id = d.id AND c.table_id = ' . self::getTableId() . ' AND c.member_id = ' . $memberId . '),0) AS is_collect,
		$builder->addFrom("App\Models\EwData", 'd');
		$builder->andWhere("d.is_del = " . self::DEL_FALSE);

		//target_id 获取相关推荐列表
		if (isset($params->target_id) && $params->target_id){
			$arr = new \stdClass();
			$arr->table_id = self::getTableId();
			$arr->list_id  = $params->target_id;
			$data = RecoService::getRecoList($arr);
			if (count($data) > 0){
				$builder->inWhere('d.id', $data);
			}
		}

		//city_id
		if (isset($params->city_id) && $params->city_id)
			$builder->andWhere("d.city_id in ( 0 , 1 , " . $params->city_id . " )");

		//tags
		if (isset($params->tags) && $params->tags)
			$builder->andWhere("d.tags = ". $params->tags);

		//is_display
		if (isset($params->is_display))
			$builder->andWhere("d.is_display = ". $params->is_display);

		//is_free
		if (isset($params->is_free) && $params->is_free)
			$builder->andWhere("d.is_free = ". $params->is_free);

		//cate_id
		if (isset($params->cate_id) && $params->cate_id)
			$builder->andWhere("d.cate_id = ". $params->cate_id);
		//type_id 1文字,2图左字右3多图,4视频
		if (isset($params->type_id) && $params->type_id)
			$builder->andWhere("d.type_id = ". $params->type_id);

		//count 数量
		if ($count)
			return $builder->getQuery()->execute()->count();

		// $sql = "d.order_by DESC, d.create_time DESC, d.id DESC, d.update_time DESC";
		$sql = " d.index_by DESC, d.order_by DESC, d.create_time DESC, d.id DESC, d.update_time DESC";//默认获取 首页 - data
		if (isset($params->is_reco) && $params->is_reco)//获取推荐 - banner - data
			$sql = " d.reco_by DESC, d.order_by DESC, d.create_time DESC, d.id DESC, d.update_time DESC";

		//order_by 排序
		if (isset($params->order_by) && $params->order_by)
			$sql = parent::orderBy($params->order_by, $table = "d");

		$builder->orderBy($sql);

		$builder->limit($params->page_num,($params->p-1)*$params->page_num);
		$result = $builder->getQuery();
		return $result->execute();
	}

	/**
	 * [getSearch 列表]
	 * @param  [type] $params [description]
	 * @return [type]         [description]
	 */
	public static function getSearch($params, $count = FALSE)
	{

		$memberId = property_exists($params,'member_id') ? $params->member_id : 0;
		$builder = new \Phalcon\Mvc\Model\Query\Builder();

		$builder->columns(['d.id,d.id as go_url,d.title,d.tags,d.cate_id,d.author,
			IFNULL((SELECT c.name AS cate_name FROM App\Models\EwCats c where d.cate_id = c.id),0) AS cate_name,
			IFNULL((d.summary),"") AS summary,
			d.pics,d.thumb,d.price,d.sources,d.is_free,
			(SELECT COUNT(b.id) AS pay_num FROM App\Models\EwBill b where d.id = b.data_id AND b.state = ' . self::STATE_PAYED . ') AS pay_num,
			d.is_display,d.order_by,d.create_time']);
		$builder->addFrom("App\Models\EwData", 'd');
		$builder->andWhere("d.is_del = " . self::DEL_FALSE);

		//position_id 通过position_id 获取后台弹出列表  1 首页
		if (isset($params->position_id) && $params->position_id == 2) 
			$builder->andWhere("d.reco_by  != '2' AND d.index_by != '2' AND d.is_display = 1");
		if (isset($params->position_id) && $params->position_id == 1) //数据首页
			$builder->andWhere("d.reco_by  != '2' AND d.index_by != '2' AND d.is_display = 1");

		//reco_by
		if (isset($params->reco_by))//首页banner列表 reco_by = 2
			$builder->andWhere("d.reco_by = '" . $params->reco_by . "'");

		//index_by
		if (isset($params->index_by))//首页视频列表 index_by = 2
			$builder->andWhere("d.index_by = '" . $params->index_by . "'");

		//city_id
		if (isset($params->city_id) && $params->city_id)
			$builder->andWhere("d.city_id in ( 0 , 1 , " . $params->city_id . " )");

		//is_display
		if (isset($params->is_display))
			$builder->andWhere("d.is_display = ". $params->is_display);
		//is_free
		if (isset($params->is_free))
			$builder->andWhere("d.is_free = ". $params->is_free);
		//title
		if (isset($params->title) && $params->title)
			$builder->andwhere("d.title LIKE '%" . $params->title . "%'");
		//short_title
		if (isset($params->short_title) && $params->short_title)
			$builder->andwhere("d.short_title LIKE '%" . $params->short_title . "%'");
		//summary
		if (isset($params->summary) && $params->summary)
			$builder->andwhere("d.summary LIKE '%" . $params->summary . "%'");
		//tags
		if (isset($params->tags) && $params->tags)
			$builder->andwhere("d.tags LIKE '%" . $params->tags . "%'");
		//content
		if (isset($params->content) && $params->content)
			$builder->andwhere("d.content LIKE '%" . $params->content . "%'");
		//sources 来源
		if (isset($params->sources) && $params->sources)
			$builder->andwhere('d.sources LIKE "%'.$params->sources.'%"');
		//author
		if (isset($params->author) && $params->author)
			$builder->andwhere("d.author LIKE '%" . $params->author . "%'");
		//cate_id
		if (isset($params->cate_id) && $params->cate_id)
			$builder->andWhere("d.cate_id = ". $params->cate_id);
		//type_id 1文字,2图左字右,3多图,4视频
		if (isset($params->type_id) && $params->type_id)
			$builder->andWhere("d.type_id = ". $params->type_id);

		//count 数量
		if ($count)
			return $builder->getQuery()->execute()->count();

		$sql = "d.order_by DESC, d.create_time DESC, d.id DESC, d.update_time DESC";
		//order_by 排序
		if (isset($params->order_by))
			$sql = parent::orderBy($params->order_by, $table = "d");

		$builder->orderBy($sql);

		$builder->limit($params->page_num,($params->p-1)*$params->page_num);
		$result = $builder->getQuery();
		return $result->execute();
	}


	/**
	 * [getTableName 获取数据表名称]
	 * @return [type] [description]
	 */
	public static function getTableName()
	{
		$model = new EwData;
		return $model->getSource();
	}

	/**
	 * [getTableId 获取数据表Id]
	 * @return [type] [description]
	 */
	public static function getTableId()
	{
		return ConfigsService::getTable(self::getTableName());
	}

	public static function getModel($id = 0)
	{

		return EwData::findFirst($id);
	}

	/**
	 * [getRecoInfo 通过id查询数据 相关推荐]
	 * @param  [type] $reco_ids [description]
	 * @return [type]           [description]
	 */
	public static function getRecoInfo($id)
	{
		$arr = new \stdClass();
		$arr->table_id = self::getTableId();
		$arr->list_id  = $id;
		$data = RecoService::getRecoList($arr);
		if(empty($data)) return $data;
		$query = EwData::query();
		$query->columns(['id,title,summary,author,price,sources,create_time']);
		$query->inWhere('id', $data);
		
		$data = $query->execute();

		if ($data){
			return $data;
		}
	}

	/**
	 * [getInfo 通过主键获取数据详情]
	 * @param  integer $id [description]
	 * @return [type]      [description]
	 */
	public static function getInfo($id = 0, $member_id = NULL)
	{
		$info = EwData::findFirst($id);
		if (!$info)
			return NULL;
		$data = new \stdClass();
		$data->go_url  = "";
		$data->groupId = self::getTableId();
		$data->praise = (string)0;
		$data->is_praise = (string)-1;
		$data->is_pay = (string)0;
		$data->pay_num = (string)0;

		$arr = new \stdClass();
		$arr->table_id = self::getTableId();
		$arr->list_id  = $id;
		$arr->praise   = NULL;
		//获取点赞量
		$data->praise = (string)MemberPraiseService::sum($arr);
		//获取当前用户是否点赞
		if ($member_id){
			$arr->member_id = $member_id;
			$data->is_praise = (string)MemberPraiseService::juge($arr);
		}

		foreach ($info->toArray() as $key => $value) {
			$data->$key = $value;
		}
		$data->publish_time = isset($data->publish_time) ? date("Y-m-d"." 00:00:00", strtotime($data->publish_time)) : date("Y-m-d"." 00:00:00", time());
		//本报告付费数量
		$data->pay_num = (string) EwBill::count(['conditions' => 'data_id = '. $id . ' AND state = ' . self::STATE_PAYED]);
		//当前登陆用户是否付费
		if ($info->is_free != self::FREE_TRUE){ //付费报告
			if ($member_id && EwBill::findFirst(['conditions' => 'data_id = ' . $id . ' AND state = ' . self::STATE_PAYED . ' AND member_id = ' . $member_id])){//未登录会员无法查看
				$data->is_pay = "1";
			}//登陆 并且 付费查看
		}
		return $data;
	}

	/**
	 * [count 获取总量]
	 * @return [type] [description]
	 */
	public static function count()
	{
		return EwData::count();
	}

	/**
	 * [getStick 置顶]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function getStick($id)
	{
		if (isset($id) && $id)
			$raw = EwData::findFirst($id);

		if ($raw){
			$max = EwData::maximum(['column' => 'order_by']);
			$raw->order_by = $max + 1;
			return $raw->save();
		}
		return NULL;
	}

	/**
	 * [download 下载量]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function download($id)
	{
		if (isset($id) && $id)
			$data = EwData::findFirst($id);

		if ($data){
			$data->download = $data->download + 1;
			return $data->save();
		}

		return NULL;
	}

	/**
	 * [visit 浏览量]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function visit($id)
	{
		if (isset($id) && $id)
			$data = EwData::findFirst($id);

		if ($data){
			$data->visit = $data->visit + 1;
			return $data->save();
		}

		return NULL;
	}

	/**
	 * [share 分享量]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function share($id)
	{
		if (isset($id) && $id)
			$data = EwData::findFirst($id);

		if ($data){
			$data->share = $data->share + 1;
			return $data->save();
		}

		return NULL;
	}

	/**
	 * [display 上架]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function display($id)
	{
		if (isset($id) && $id)
			$data = EwData::findFirst($id);

		if ($data){
			$data->is_display = $data->is_display ? self::DISPLAY_FALSE : self::DISPLAY_TRUE;
			return $data->save();
		}

		return NULL;
	}

	/**
	 * [delete 移除/恢复数据]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function delete($id)
	{
		if (isset($id) && $id)
			$data = EwData::findFirst($id);

		if ($data){
			$data->is_del = $data->is_del ? self::DEL_FALSE : self::DEL_TRUE;
			return $data->save();
		}

		return NULL;
	}

	/**
	 * [delete 彻底删除数据]
	 * @param  [type] $id [description]
	 * @return [type]     [description]
	 */
	public static function forever($id)
	{
		if (isset($id) && $id)
			$row = EwData::findFirst($id);

		if ($row && $row->delete()){
			$arr = new \stdClass();
		 	$arr->table_id = self::getTableId();
			$arr->list_id  = $id;
			RecoService::deleteRecoList($arr);
			return TRUE;
		}

		return NULL;
	}

	/**
	 * [save 保存数据]
	 * @param  [type] $data [description]
	 * @return [type]       [description]
	 */
	public static function save($data)
	{
		$data->city_id = 0;
		$row = new EwData;
		if (isset($data->id) && $data->id)
			$row = EwData::findFirst($data->id);

		
		//发布时间
		$data->publish_time = isset($data->publish_time) ? date("Y-m-d"." 00:00:00", strtotime($data->publish_time)) : date("Y-m-d"." 00:00:00", time());
		//置顶
		if (isset($data->is_stick) && $data->is_stick){
			$max = EwData::maximum(['column' => 'order_by']);
			$data->order_by = $max + 1;
		}

		//判断价格是否免费 如果价格为0
		if (floatval($data->price) > 0){
			$data->cate_id = 10;
			$data->is_free = self::FREE_FALSE;
		}else{
			$data->cate_id = 9;
			$data->is_free = self::FREE_TRUE;
		}
		
		if($row && $row -> save((array)$data)) {
		 	//推荐id插入推荐表
		 	$arr = new \stdClass();
		 	$arr->table_id = self::getTableId();
			$arr->list_id  = $row->id;
			$arr->reco_ids = $data->reco_ids;
		 	RecoService::saveRecoList($arr);
	    }		
	}

	/**
	 * 2017-3-28 +
	 * [batchReco 批量 推荐/批量删除 到 推荐列表  首页列表  同时置顶] 
	 * @param  [type]  $id_arr [操作的id 数组形式]
	 * @param  integer $action [2推荐到推荐列表 1推荐到首页列表 默认取消推荐]
	 * @return [type]          [description]
	 */
	public static function batchReco($id_arr = [], $action = FALSE )
	{
		//查询最大的order_by
		$max = EwData::maximum(['column' => 'order_by']);
		$reco_by  = 0;
		$index_by = 0;
		switch ($action) {
			case 2://推荐到推荐列表
				$reco_by  = 2;
				$index_by = 0;
				$order_by = $max + 1;
				break;

			case 1://推荐到首页列表
				$reco_by  = 0;
				$index_by = 2;
				$order_by = $max + 1;
				break;

			default://取消推荐
				$reco_by  = 0;
				$index_by = 0;
				$order_by = 100;
				break;
		}

		if ($id_arr && is_array($id_arr) && count($id_arr) > 0){
			foreach ($id_arr as $id) {
				$row = EwData::findFirst($id);
				$row->reco_by  = $reco_by;
				$row->index_by = $index_by;
				$row->order_by = $order_by;
				$row->save();
			}
		}
	}
    /**
     * [getSummary 列表]
     * @param  [type] $params [description]
     * @return [type]         [description]
     */
    public static function getSummary($params)
    {
        $lastday=date("Y-m-d",strtotime("-1 day"));
        $today=date("Y-m-d");
        $summary_renthouse="SELECT concat(s.cname,'汇总') plat,s.nos totals,(SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='400' and substr(d.create_time,1,10)='{$today}' ) today_add_total,s.offline_nos today_offline   ,
                    (SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='400' and substr(d.create_time,1,10)='{$lastday}' ) yestday_add_total,(SELECT SUM(d.offline_nos) from ew_summarydate d where d.ctype='400' and substr(d.create_time,1,10)='{$lastday}') yest_offline
                    from ew_summarydate s where ctype='4'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,SUM(s.online_nos) today_add_total,SUM(s.offline_nos) today_offline   ,
                    (SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='400' and s.plat_type=d.plat_type  and substr(d.create_time,1,10)='{$lastday}' LIMIT 1) yestday_add_total,
                    (SELECT SUM(d.offline_nos) from ew_summarydate d where d.ctype='400' and s.plat_type=d.plat_type  and substr(d.create_time,1,10)='{$lastday}' LIMIT 1) yest_offline
                    from ew_summarydate s where ctype='400' and substr(s.create_time,1,10)=substr(NOW(),1,10) GROUP BY s.plat_type
         ";

        $summary_sechouse="SELECT concat(s.cname,'汇总') plat,s.nos totals,(SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='300' and substr(d.create_time,1,10)='{$today}' ) today_add_total,s.offline_nos today_offline   ,
                    (SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='300' and substr(d.create_time,1,10)='{$lastday}' ) yestday_add_total,(SELECT SUM(d.offline_nos) from ew_summarydate d where d.ctype='300' and substr(d.create_time,1,10)='{$lastday}') yest_offline
                    from ew_summarydate s where ctype='3'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,SUM(s.online_nos) today_add_total,SUM(s.offline_nos) today_offline   ,
                    (SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='300' and s.plat_type=d.plat_type  and substr(d.create_time,1,10)='{$lastday}' LIMIT 1) yestday_add_total,
                    (SELECT SUM(d.offline_nos) from ew_summarydate d where d.ctype='300' and s.plat_type=d.plat_type  and substr(d.create_time,1,10)='{$lastday}' LIMIT 1) yest_offline
                    from ew_summarydate s where ctype='300' and substr(s.create_time,1,10)=substr(NOW(),1,10) GROUP BY s.plat_type";

        $summary_newhouse="SELECT concat(s.cname,'汇总') plat,s.nos totals,(SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='200' and substr(d.create_time,1,10)='{$today}' ) today_add_total,s.offline_nos today_offline   ,
                    (SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='200' and substr(d.create_time,1,10)='{$lastday}' ) yestday_add_total,(SELECT SUM(d.offline_nos) from ew_summarydate d where d.ctype='200' and substr(d.create_time,1,10)='{$lastday}') yest_offline
                    from ew_summarydate s where ctype='2'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,SUM(s.online_nos) today_add_total,SUM(s.offline_nos) today_offline   ,
                    (SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='200' and s.plat_type=d.plat_type  and substr(d.create_time,1,10)='{$lastday}' LIMIT 1) yestday_add_total,
                    (SELECT SUM(d.offline_nos) from ew_summarydate d where d.ctype='200' and s.plat_type=d.plat_type  and substr(d.create_time,1,10)='{$lastday}' LIMIT 1) yest_offline
                    from ew_summarydate s where ctype='200' and substr(s.create_time,1,10)=substr(NOW(),1,10) GROUP BY s.plat_type
                     ";

        $summary_news="SELECT concat(s.cname,'汇总') plat,s.online_nos totals,(SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='100' and substr(d.create_time,1,10)='{$today}' ) today_add_total,s.offline_nos today_offline   ,
                    (SELECT SUM(d.online_nos) from ew_summarydate d where d.ctype='100' and substr(d.create_time,1,10)='{$lastday}') yestday_add_total,0 yest_offline
                    from ew_summarydate s where ctype='1'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,s.online_nos today_add_total,s.offline_nos today_offline   ,
                    (SELECT d.online_nos from ew_summarydate d where d.ctype='100'  and substr(d.create_time,1,10)='{$lastday}' and d.plat_type=s.plat_type LIMIT 1) yestday_add_total,0 yest_offline
                    from ew_summarydate s where ctype='100' and substr(s.create_time,1,10)=substr(NOW(),1,10) ";

        $summary_member="SELECT '会员总数' plat,(SELECT COUNT(*) house_number from ew_user r) as totals
                    UNION
				  SELECT (case l.login_type when 'qq' then 'QQ用户' when 'weixin' then '微信用户' when 'weibo' then '微博用户' else '手机用户'  end) plat,
				  count(m.id) from ew_member m LEFT JOIN ew_login3 l on m.id=l.member_id GROUP BY l.login_type ";
        $d=date("Y-m",strtotime("-5 month"));
        $memerline="SELECT COUNT(*) totals,  (case l.login_type when 'qq' then 'qq' when 'weixin' then 'weixin' when 'weibo' then 'weibo' else 'shouji'  end)
                                plat,substr(m.create_time,1,7) months from ew_user m LEFT JOIN ew_member ew on m.id=ew.id LEFT JOIN  ew_login3 l
                        on m.id=l.member_id where substr(m.create_time,1,7)>='{$d}' and login_type='qq'    GROUP BY substr(m.create_time,1,7)
                        union
                        SELECT COUNT(*) totals,  (case l.login_type when 'qq' then 'qq' when 'weixin' then 'weixin' when 'weibo' then 'weibo' else 'shouji'  end)
                                     plat,substr(m.create_time,1,7) months from ew_user m LEFT JOIN ew_member ew on m.id=ew.id LEFT JOIN  ew_login3 l
                        on m.id=l.member_id where substr(m.create_time,1,7)>='{$d}' and login_type='shouji'    GROUP BY substr(m.create_time,1,7)
                        union
                        SELECT COUNT(*) totals,  (case l.login_type when 'qq' then 'qq' when 'weixin' then 'weixin' when 'weibo' then 'weibo' else 'shouji'  end)
                                     plat,substr(m.create_time,1,7) months from ew_user m LEFT JOIN ew_member ew on m.id=ew.id LEFT JOIN  ew_login3 l
                        on m.id=l.member_id where substr(m.create_time,1,7)>='{$d}' and login_type='weixin'    GROUP BY substr(m.create_time,1,7)
                        union
                        SELECT COUNT(*) totals,  (case l.login_type when 'qq' then 'qq' when 'weixin' then 'weixin' when 'weibo' then 'weibo' else 'shouji'  end)
                                     plat,substr(m.create_time,1,7) months from ew_user m LEFT JOIN ew_member ew on m.id=ew.id LEFT JOIN  ew_login3 l
                        on m.id=l.member_id where substr(m.create_time,1,7)>='{$d}' and login_type='weibo'    GROUP BY substr(m.create_time,1,7)";
        $des_pic="SELECT concat(s.cname,'汇总') plat,s.nos totals,s.online_nos today_add_total,s.offline_nos today_offline   ,0 yestday_add_total,0 yest_offline
                    from ew_summarydate s where ctype='6'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,s.online_nos today_add_total,s.offline_nos today_offline   ,0 yestday_add_total,0 yest_offline
                    from ew_summarydate s where ctype='600' and substr(s.create_time,1,10)=substr(NOW(),1,10)";

        $des_compay="SELECT concat(s.cname,'汇总') plat,s.nos totals,s.online_nos today_add_total,s.offline_nos today_offline   ,0 yestday_add_total,0 yest_offline
                    from ew_summarydate s where ctype='5'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,s.online_nos today_add_total,s.offline_nos today_offline   ,0 yestday_add_total,0 yest_offline
                    from ew_summarydate s where ctype='500' and substr(s.create_time,1,10)=substr(NOW(),1,10)";

        $robot = new \App\Models\Robots();


        $rent_house_month="SELECT concat(s.cname,'汇总') plat,s.nos totals,s.online_nos month_add_total,s.offline_nos month_offline   ,0 yestday_add_total,0 yest_offline,'' months
                    from ew_summarydate s where ctype='4'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,sum(s.online_nos) month_add_total,sum(s.offline_nos) month_offline
										,0 yestday_add_total,0 yest_offline,substr(s.create_time,1,7) months
                    from ew_summarydate s where ctype='400' and substr(s.create_time,1,7)=substr(NOW(),1,7) GROUP BY s.plat_type,substr(s.create_time,1,7)";

        $sec_house_month="SELECT concat(s.cname,'汇总') plat,s.nos totals,s.online_nos month_add_total,s.offline_nos month_offline   ,0 yestday_add_total,0 yest_offline,'' months
                    from ew_summarydate s where ctype='3'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,sum(s.online_nos) month_add_total,sum(s.offline_nos) month_offline
										,0 yestday_add_total,0 yest_offline,substr(s.create_time,1,7) months
                    from ew_summarydate s where ctype='300' and substr(s.create_time,1,7)=substr(NOW(),1,7) GROUP BY s.plat_type,substr(s.create_time,1,7)";

        $new_house_month="SELECT concat(s.cname,'汇总') plat,s.nos totals,s.online_nos month_add_total,s.offline_nos month_offline   ,0 yestday_add_total,0 yest_offline,'' months
                    from ew_summarydate s where ctype='2'
                    union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,sum(s.online_nos) month_add_total,sum(s.offline_nos) month_offline
										,0 yestday_add_total,0 yest_offline,substr(s.create_time,1,7) months
                    from ew_summarydate s where ctype='200' and substr(s.create_time,1,7)=substr(NOW(),1,7) GROUP BY s.plat_type,substr(s.create_time,1,7)";
        $des_pic_month="SELECT concat(s.cname,'汇总') plat,s.nos totals,s.online_nos month_add_total,s.offline_nos month_offline   ,0 yestday_add_total,0 yest_offline,'' months
                    from ew_summarydate s where ctype='6'
										union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,SUM(s.online_nos) month_add_total,SUM(s.offline_nos) month_offline   ,0 yestday_add_total,0 yest_offline,
										substr(s.create_time,1,7)
                    from ew_summarydate s where ctype='600' and substr(s.create_time,1,7)=substr(NOW(),1,7) GROUP by s.plat_type,substr(s.create_time,1,7)";

        $des_company_month="SELECT concat(s.cname,'汇总') plat,s.nos totals,s.online_nos month_add_total,s.offline_nos month_offline   ,0 yestday_add_total,0 yest_offline,'' months
                    from ew_summarydate s where ctype='5'
										union
                    SELECT (SELECT c.val from ew_configs c where c.key_name=s.plat_type) plat,0 totals,SUM(s.online_nos) month_add_total,SUM(s.offline_nos) month_offline   ,0 yestday_add_total,0 yest_offline,
										substr(s.create_time,1,7)
                    from ew_summarydate s where ctype='500' and substr(s.create_time,1,7)=substr(NOW(),1,7) GROUP by s.plat_type,substr(s.create_time,1,7)";
        // Execute the query
        $result=$robot->getReadConnection()->query($summary_renthouse);
        $result->setFetchMode(Db::FETCH_ASSOC);


        $renthouse = $result->fetchAll();


       $result = $robot->getReadConnection()->query($summary_sechouse);
       $result->setFetchMode(Db::FETCH_ASSOC);
       $sechouse= $result->fetchAll();

       $result=$robot->getReadConnection()->query($summary_newhouse);
       $result->setFetchMode(Db::FETCH_ASSOC);
       $newhouse= $result->fetchAll();

       $result = $robot->getReadConnection()->query($summary_news);
       $result->setFetchMode(Db::FETCH_ASSOC);
       $news= $result->fetchAll();

        $result = $robot->getReadConnection()->query($summary_member);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $member= $result->fetchAll();

        $result = $robot->getReadConnection()->query($des_pic);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $despic= $result->fetchAll();

        $result = $robot->getReadConnection()->query($des_compay);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $descompay= $result->fetchAll();

        $result = $robot->getReadConnection()->query($rent_house_month);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $rent_month= $result->fetchAll();

        $result = $robot->getReadConnection()->query($sec_house_month);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $second_month= $result->fetchAll();

        $result = $robot->getReadConnection()->query($new_house_month);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $newhouse_month= $result->fetchAll();

        $result = $robot->getReadConnection()->query($des_pic_month);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $despic_month= $result->fetchAll();

        $result = $robot->getReadConnection()->query($des_company_month);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $descompay_month= $result->fetchAll();


        $result = $robot->getReadConnection()->query($memerline);
        $result->setFetchMode(Db::FETCH_ASSOC);
        $memline= $result->fetchAll();
        $line=self:: getMemberline($memline);

        return array('renthouse'=>$renthouse,'sechouse'=>$sechouse,'newhouse'=>$newhouse,'news'=>$news,'member'=>$member,'despic'=>$despic,'descompay'=>$descompay,
            'renthousemonth'=>$rent_month,'sechousemonth'=>$second_month,'newhousemonth'=>$newhouse_month,'despicmonth'=>$despic_month,'descompanymonth'=>$descompay_month,"membline"=>$line);
    }
    public static function getMemberline($data){

        $plat=array("shouji"=>array(0,0,0,0,0,0),'qq'=>array(0,0,0,0,0,0),'weibo'=>array(0,0,0,0,0,0),'weixin'=>array(0,0,0,0,0,0));
        $mothlist=[date("Y-m",strtotime("-5 month")),date("Y-m",strtotime("-4 month")),date("Y-m",strtotime("-3 month")),date("Y-m",strtotime("-2 month")),date("Y-m",strtotime("-1 month")),date("Y-m")];




        foreach($data as $keys=>$vals){
                $key = array_search($vals['months'], $mothlist);
                $plat_key=$vals['plat'];
                $plat[$plat_key][$key]=intval($vals['totals']);
            }

        return array(
            'item'=>array(array('name'=>'手机注册用户','data'=>$plat['shouji']),array('name'=>'QQ注册用户','data'=>$plat['qq']),
                array('name'=>'微博注册用户','data'=>$plat['weibo']),array('name'=>'微信注册用户','data'=>$plat['weixin'])),
            'months'=>$mothlist
        );


    }
    /**
     * [getSummaryDetail 列表]
     * @param  [type] $params [description]
     * @return [type]         [description]
     */
    public static function getSummaryDetail($params)
    {
        $plat='1';
        if($plat=='1'){
            $summary_renthouse="SELECT COUNT(*) totals,(SELECT d.name from ew_dics d where d.id=r.city_id) cityname
                  from ew_house_rent r where r.sources='安居客' and substr(r.create_time,1,10)=substr(NOW(),1,10) GROUP BY r.city_id";
        }else if($plat=='2'){
            $summary_renthouse="SELECT COUNT(*) totals,(SELECT d.name from ew_dics d where d.id=r.city_id) cityname
                  from ew_house_reco r where r.sources='安居客' and substr(r.create_time,1,10)=substr(NOW(),1,10) GROUP BY r.city_id";
        }else if($plat=='3'){
            $summary_renthouse="SELECT COUNT(*) totals,(SELECT d.name from ew_house d where d.id=r.city_id) cityname
                  from ew_house_reco r where r.sources='安居客' and substr(r.create_time,1,10)=substr(NOW(),1,10) GROUP BY r.city_id";
        }

        "SELECT COUNT(*) totals,  (case l.login_type when 'qq' then 'qq' when 'weixin' then 'weixin' when 'weibo' then 'weibo' else 'shouji'  end)
plat,substr(m.create_time,1,7) months from ew_user m LEFT JOIN ew_member ew on m.id=ew.id LEFT JOIN  ew_login3 l on m.id=l.member_id   GROUP BY substr(m.create_time,1,7), plat";

        $robot = new \App\Models\Robots();

        // Execute the query
        $result=$robot->getReadConnection()->query($summary_renthouse);
        $result->setFetchMode(Db::FETCH_ASSOC);


        $renthouse = $result->fetchAll();



        return array('houselist'=>$renthouse);
    }

}